package com.xuanzheng.company.dao.impl;

import com.xuanzheng.company.dao.AnnouncementDao;
import com.xuanzheng.company.entity.Announcement;
import com.xuanzheng.company.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AnnouncementDaoImpl implements AnnouncementDao {

    @Override
    public boolean addAnnouncement(Announcement announcement) {
        String sql = "INSERT INTO company_announcement (id, company_id, title, content, publisher_id, publisher_name) " +
                     "VALUES (?, ?, ?, ?, ?, ?)";
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, announcement.getId());
            pstmt.setString(2, announcement.getCompanyId());
            pstmt.setString(3, announcement.getTitle());
            pstmt.setString(4, announcement.getContent());
            pstmt.setString(5, announcement.getPublisherId());
            pstmt.setString(6, announcement.getPublisherName());
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
    }

    @Override
    public boolean updateAnnouncement(Announcement announcement) {
        String sql = "UPDATE company_announcement SET title = ?, content = ? WHERE id = ?";
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, announcement.getTitle());
            pstmt.setString(2, announcement.getContent());
            pstmt.setString(3, announcement.getId());
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
    }

    @Override
    public boolean deleteAnnouncement(String id) {
        String sql = "DELETE FROM company_announcement WHERE id = ?";
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
    }

    @Override
    public Announcement getAnnouncementById(String id) {
        String sql = "SELECT * FROM company_announcement WHERE id = ?";
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return extractAnnouncementFromResultSet(rs);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
    }

    @Override
    public List<Announcement> getAnnouncementsByCompanyId(String companyId) {
        String sql = "SELECT * FROM company_announcement WHERE company_id = ? ORDER BY publish_time DESC";
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Announcement> announcements = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            
            rs = pstmt.executeQuery();
            while (rs.next()) {
                announcements.add(extractAnnouncementFromResultSet(rs));
            }
            return announcements;
        } catch (SQLException e) {
            e.printStackTrace();
            return announcements;
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
    }
    
    private Announcement extractAnnouncementFromResultSet(ResultSet rs) throws SQLException {
        Announcement announcement = new Announcement();
        announcement.setId(rs.getString("id"));
        announcement.setCompanyId(rs.getString("company_id"));
        announcement.setTitle(rs.getString("title"));
        announcement.setContent(rs.getString("content"));
        announcement.setPublisherId(rs.getString("publisher_id"));
        announcement.setPublisherName(rs.getString("publisher_name"));
        announcement.setPublishTime(rs.getTimestamp("publish_time"));
        announcement.setUpdateTime(rs.getTimestamp("update_time"));
        return announcement;
    }
} 